﻿using Dapper;
using Domain.Entity.System_operation;
using Domain.Entity.System_Ordermanagement;
using Domain.Entity.System_setting;
using Domain.IRepository.System_operation;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.DependencyInjection;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Volo.Abp.DependencyInjection;

namespace EFCore.Repository.System_operation
{
    [Dependency(ServiceLifetime.Scoped)]
    public class Receipt_meterRepository:IReceipt_meterRepository
    {
        public MyDbContext myDbContext { get; set; }

        public Receipt_meterRepository(MyDbContext myDbContext)
        {
            this.myDbContext = myDbContext;
        }

        public int Count(string? Sign_time, string? value2, string? Receipt_type, string? Ord_state)
        {
            string sql = "select B.Ord_number,B.Client_name,A.Receipt_type,B.Ord_state,A.Receipt_num,B.Ord_startaddress," +
                "B.Ord_destination,C.Sign_num,C.Sign_by,C.Sign_time FROM" +
                " Receipt_meter A JOIN Orders B ON A.Ord_id = B.Ord_id " +
                "JOIN Signature_receipt C ON A.Sign_id = C.Sign_id";
            if (Sign_time != null && Sign_time != "")
            {
                string[] time1 = Sign_time.Split(',');
                sql += $" and [Sign_time]>='{time1[0]}' and [Sign_time]<='{time1[1]}'";
            }
            if (value2 != null && value2 != "")
            {
                sql += $"AND (Customer LIKE '%{value2}%')";
            }
            if (Receipt_type != null && Receipt_type != "")
            {
                sql += $" and [Receipt_type] like '%{Receipt_type}%'";
            }
            if (Ord_state != null && Ord_state != "")
            {
                sql += $" and [Ord_state] like '%{Ord_state}%'";
            }
            int count = myDbContext.Database.GetDbConnection().Query<Receipt_meter>(sql).Count();
            return count;
        }

        public List<Receipt_meter> GetReceipt_meter()
        {
            string sql = "SELECT * FROM [dbo].[Receipt_meter]";
            return myDbContext.Database.GetDbConnection().Query<Receipt_meter>(sql).ToList();
        }

        public Orders GetOrd_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Orders] where [Ord_id]={id}";
            return myDbContext.Database.GetDbConnection().Query<Orders>(sql).FirstOrDefault();
        }

        public Signature_receipt GetSign_id(int id)
        {
            string sql = $"SELECT * FROM [dbo].[Signature_receipt] where [Sign_id]={id}";
            return myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).FirstOrDefault();
        }

        public int BC(Receipt_meter receipt_Meter)
        {
            string sql = $@"update Receipt_meter set Receipt_type=1,[Courier_companies]={receipt_Meter.Courier_companies},Mailing_number='{receipt_Meter.Mailing_number}',Mailing_time='{receipt_Meter.Mailing_time}',Receipt='{receipt_Meter.Receipt}' where Receipt_id='{receipt_Meter.Receipt_id}'";
            return myDbContext.Database.GetDbConnection().Execute(sql);
        }

        public Signature_receipt GetById()
        {
            string sql = "SELECT * FROM dbo.Signature_receipt";
            return myDbContext.Database.GetDbConnection().Query<Signature_receipt>(sql).ToList()[0];
        }
    }
}
